const express = require('express')
const mysql = require('mysql2/promise')
const cors = require('cors')
const bodyParser = require('body-parser')

const app = express()
const PORT = process.env.PORT || 3001

// 中间件
app.use(cors())
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({ extended: true }))

// 数据库连接配置
const dbConfig = {
  host: '47.79.16.84',
  user: '623',
  password: 'dyHBknZEzYCDCDJb',
  database: '623'
}

// 创建数据库连接池
let pool

async function initDatabase() {
  try {
    // 创建连接池
    pool = mysql.createPool(dbConfig)

    // 测试数据库连接
    const connection = await pool.getConnection()
    console.log('数据库连接成功')
    connection.release()

    // 创建数据表
    await createTables()
    console.log('数据表初始化完成')

    // 插入示例数据
    await insertSampleData()
    console.log('示例数据插入完成')

  } catch (error) {
    console.error('数据库初始化失败:', error)
    console.error('请检查数据库连接信息和网络连接')
    process.exit(1)
  }
}

// 创建数据表
async function createTables() {
  const connection = await pool.getConnection()
  
  try {
    // 创建班级表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS classes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        grade VARCHAR(10) NOT NULL,
        student_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `)

    // 创建学生表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id VARCHAR(20) UNIQUE NOT NULL,
        name VARCHAR(50) NOT NULL,
        gender ENUM('男', '女') NOT NULL,
        age INT NOT NULL,
        class_name VARCHAR(100) NOT NULL,
        phone VARCHAR(20),
        email VARCHAR(100),
        address TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `)

    // 创建课程表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS courses (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(20) UNIQUE NOT NULL,
        name VARCHAR(100) NOT NULL,
        credits INT NOT NULL,
        teacher VARCHAR(50) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `)

    // 创建成绩表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS grades (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id VARCHAR(20) NOT NULL,
        course_code VARCHAR(20) NOT NULL,
        score DECIMAL(5,2) NOT NULL,
        semester VARCHAR(10) NOT NULL,
        exam_date DATE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (student_id) REFERENCES students(student_id),
        FOREIGN KEY (course_code) REFERENCES courses(code)
      )
    `)

  } finally {
    connection.release()
  }
}

// 插入示例数据
async function insertSampleData() {
  const connection = await pool.getConnection()
  
  try {
    // 检查是否已有数据
    const [existingStudents] = await connection.execute('SELECT COUNT(*) as count FROM students')
    if (existingStudents[0].count > 0) {
      console.log('数据已存在，跳过示例数据插入')
      return
    }

    // 插入班级数据
    const classes = [
      ['计算机科学与技术1班', '2023', 30],
      ['计算机科学与技术2班', '2023', 28],
      ['软件工程1班', '2023', 25],
      ['软件工程2班', '2023', 27],
      ['网络工程班', '2023', 22]
    ]

    for (const cls of classes) {
      await connection.execute(
        'INSERT INTO classes (name, grade, student_count) VALUES (?, ?, ?)',
        cls
      )
    }

    // 插入课程数据
    const courses = [
      ['MATH101', '高等数学', 4, '张教授'],
      ['CS101', '程序设计基础', 3, '李老师'],
      ['CS201', '数据结构', 3, '王老师'],
      ['CS301', '数据库原理', 3, '刘教授'],
      ['CS302', '计算机网络', 3, '陈老师']
    ]

    for (const course of courses) {
      await connection.execute(
        'INSERT INTO courses (code, name, credits, teacher) VALUES (?, ?, ?, ?)',
        course
      )
    }

    // 插入学生数据
    const students = [
      ['2023001001', '张三', '男', 20, '计算机科学与技术1班', '13800138001', 'zhangsan@example.com', '北京市朝阳区'],
      ['2023001002', '李四', '女', 19, '计算机科学与技术1班', '13800138002', 'lisi@example.com', '上海市浦东新区'],
      ['2023001003', '王五', '男', 21, '计算机科学与技术2班', '13800138003', 'wangwu@example.com', '广州市天河区'],
      ['2023001004', '赵六', '女', 20, '软件工程1班', '13800138004', 'zhaoliu@example.com', '深圳市南山区'],
      ['2023001005', '钱七', '男', 19, '软件工程2班', '13800138005', 'qianqi@example.com', '杭州市西湖区'],
      ['2023001006', '孙八', '女', 20, '网络工程班', '13800138006', 'sunba@example.com', '南京市鼓楼区'],
      ['2023001007', '周九', '男', 21, '计算机科学与技术1班', '13800138007', 'zhoujiu@example.com', '武汉市洪山区'],
      ['2023001008', '吴十', '女', 19, '计算机科学与技术2班', '13800138008', 'wushi@example.com', '成都市锦江区']
    ]

    for (const student of students) {
      await connection.execute(
        'INSERT INTO students (student_id, name, gender, age, class_name, phone, email, address) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        student
      )
    }

    // 插入成绩数据
    const grades = [
      ['2023001001', 'MATH101', 85.5, '2023-1', '2023-12-15'],
      ['2023001001', 'CS101', 92.0, '2023-1', '2023-12-18'],
      ['2023001002', 'MATH101', 78.0, '2023-1', '2023-12-15'],
      ['2023001002', 'CS101', 88.5, '2023-1', '2023-12-18'],
      ['2023001003', 'CS201', 87.0, '2023-2', '2024-06-10'],
      ['2023001004', 'CS301', 91.5, '2023-2', '2024-06-12']
    ]

    for (const grade of grades) {
      await connection.execute(
        'INSERT INTO grades (student_id, course_code, score, semester, exam_date) VALUES (?, ?, ?, ?, ?)',
        grade
      )
    }

  } finally {
    connection.release()
  }
}

// API 路由

// 获取所有学生
app.get('/api/students', async (req, res) => {
  try {
    const [rows] = await pool.execute(`
      SELECT s.*, 
             (SELECT AVG(g.score) FROM grades g WHERE g.student_id = s.student_id) as avg_score
      FROM students s
      ORDER BY s.created_at DESC
    `)
    res.json(rows)
  } catch (error) {
    console.error('获取学生列表失败:', error)
    res.status(500).json({ error: '服务器错误' })
  }
})

// 获取单个学生
app.get('/api/students/:id', async (req, res) => {
  try {
    const [rows] = await pool.execute('SELECT * FROM students WHERE id = ?', [req.params.id])
    if (rows.length === 0) {
      return res.status(404).json({ error: '学生不存在' })
    }
    res.json(rows[0])
  } catch (error) {
    console.error('获取学生详情失败:', error)
    res.status(500).json({ error: '服务器错误' })
  }
})

// 添加学生
app.post('/api/students', async (req, res) => {
  const { student_id, name, gender, age, class_name, phone, email, address } = req.body
  
  try {
    const [result] = await pool.execute(
      'INSERT INTO students (student_id, name, gender, age, class_name, phone, email, address) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [student_id, name, gender, age, class_name, phone, email, address]
    )
    
    const [newStudent] = await pool.execute('SELECT * FROM students WHERE id = ?', [result.insertId])
    res.status(201).json(newStudent[0])
  } catch (error) {
    console.error('添加学生失败:', error)
    if (error.code === 'ER_DUP_ENTRY') {
      res.status(400).json({ error: '学号已存在' })
    } else {
      res.status(500).json({ error: '服务器错误' })
    }
  }
})

// 更新学生
app.put('/api/students/:id', async (req, res) => {
  const { name, gender, age, class_name, phone, email, address } = req.body
  
  try {
    await pool.execute(
      'UPDATE students SET name = ?, gender = ?, age = ?, class_name = ?, phone = ?, email = ?, address = ? WHERE id = ?',
      [name, gender, age, class_name, phone, email, address, req.params.id]
    )
    
    const [updatedStudent] = await pool.execute('SELECT * FROM students WHERE id = ?', [req.params.id])
    res.json(updatedStudent[0])
  } catch (error) {
    console.error('更新学生失败:', error)
    res.status(500).json({ error: '服务器错误' })
  }
})

// 删除学生
app.delete('/api/students/:id', async (req, res) => {
  try {
    const [result] = await pool.execute('DELETE FROM students WHERE id = ?', [req.params.id])
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: '学生不存在' })
    }
    res.json({ message: '删除成功' })
  } catch (error) {
    console.error('删除学生失败:', error)
    res.status(500).json({ error: '服务器错误' })
  }
})

// 获取所有班级
app.get('/api/classes', async (req, res) => {
  try {
    const [rows] = await pool.execute('SELECT * FROM classes ORDER BY grade DESC, name')
    res.json(rows)
  } catch (error) {
    console.error('获取班级列表失败:', error)
    res.status(500).json({ error: '服务器错误' })
  }
})

// 获取所有课程
app.get('/api/courses', async (req, res) => {
  try {
    const [rows] = await pool.execute('SELECT * FROM courses ORDER BY code')
    res.json(rows)
  } catch (error) {
    console.error('获取课程列表失败:', error)
    res.status(500).json({ error: '服务器错误' })
  }
})

// 获取统计数据
app.get('/api/statistics', async (req, res) => {
  try {
    const [studentCount] = await pool.execute('SELECT COUNT(*) as total FROM students')
    const [classCount] = await pool.execute('SELECT COUNT(*) as total FROM classes')
    const [courseCount] = await pool.execute('SELECT COUNT(*) as total FROM courses')
    const [avgScore] = await pool.execute('SELECT AVG(score) as average FROM grades')
    const [genderStats] = await pool.execute(`
      SELECT gender, COUNT(*) as count 
      FROM students 
      GROUP BY gender
    `)
    
    res.json({
      totalStudents: studentCount[0].total,
      totalClasses: classCount[0].total,
      totalCourses: courseCount[0].total,
      averageScore: avgScore[0].average || 0,
      genderDistribution: genderStats
    })
  } catch (error) {
    console.error('获取统计数据失败:', error)
    res.status(500).json({ error: '服务器错误' })
  }
})

// 健康检查
app.get('/api/health', (req, res) => {
  res.json({ 
    status: 'ok', 
    message: '学生管理系统API运行正常',
    timestamp: new Date().toISOString()
  })
})

// 错误处理中间件
app.use((err, req, res, next) => {
  console.error('服务器错误:', err)
  res.status(500).json({ error: '内部服务器错误' })
})

// 404 处理
app.use((req, res) => {
  res.status(404).json({ error: 'API路径不存在' })
})

// 启动服务器
async function startServer() {
  try {
    await initDatabase()
    
    app.listen(PORT, () => {
      console.log(`=================================`)
      console.log(`🚀 学生管理系统后端服务启动成功！`)
      console.log(`📱 服务地址: http://localhost:${PORT}`)
      console.log(`🔗 API文档: http://localhost:${PORT}/api/health`)
      console.log(`💾 数据库: ${dbConfig.host}:3306/${dbConfig.database}`)
      console.log(`=================================`)
    })
  } catch (error) {
    console.error('服务器启动失败:', error)
    process.exit(1)
  }
}

// 优雅关闭
process.on('SIGTERM', async () => {
  console.log('正在关闭服务器...')
  if (pool) {
    await pool.end()
  }
  process.exit(0)
})

process.on('SIGINT', async () => {
  console.log('正在关闭服务器...')
  if (pool) {
    await pool.end()
  }
  process.exit(0)
})

startServer() 